Dynamic text is user-built data-driven text that is linked to a data discovery. The text includes dynamic PQL or MDX functions in order to obtain and display the required information.
Dynamic text is constructed in the Dynamic Text Editor in Illustrate, Present, or Publish. Dynamic text is added to presentations and publications; when rendered, the dynamic text shows the current data-driven values of the given dynamic functions.
Dynamic PQL Functions
- Click here to learn how to configure dynamic PQL functions in Illustrate
- Click here to review the dynamic PQL functions in Illustrate
Opening the Dynamic Text Editor
Dynamic text is configured in the Dynamic Text Editor (purple highlight below). To open the editor:
- Add a textbox to the canvas.
- With the textbox selected (red highlight below), go to the Text ribbon.
- Click the Dynamic Text button (yellow highlight below).
Setting Dynamic Text Properties
You have the option to set dynamic properties (yellow highlight below). These properties can be used to show or hide the dynamic text, change its size, or change its rotation.
Set the Actual Value (that is, the value that drives the dynamic text) via the Dynamic Text Editor. The Condition Value can be a given static value, or based on a dynamic formula.
- Click here to learn about the dynamic text properties.
Using the Dynamic Text Editor
To add a dynamic text variable and set up the dynamic function that replaces it:
- Add a Text Field to the canvas and click Dynamic Text > Dynamic Text from the Component ribbon to incorporate the variable.
- Open the Content drop-down (orange arrow above) and select either the Grid from a discovery or the visual area from a tabulation that you want to base your dynamic formula on.
- Build your dynamic expression in the script editor (blue arrow):
- Use the PQL Functions library (yellow highlight) to add SQL expressions to your script. You can hover over the items in the tree to view details of each function and double-click to copy a function into the script editor.
- Select the relevant cells from the grid to populate the given PQL expressions with data. You can click data cells to add the cell value to your expression or, if your source is a matrix grid, click the label, column header, or row header to add the respective content or total of the column or row values instead.
- Once you are happy with your dynamic expression, you should Test your expression and then click Set to create your dynamic function and close the wizard.
The Dynamic Function wizard opens at the bottom of the page:
Note: This panel opens 1) When you first add the Dynamic Text field (as in this step) and 2) When you click the green variable on the canvas after it has been created.
Once selected, the Grid or visual area opens in the editor (green arrow).
Note: The example above replaces the variable on the canvas with the label from the cell at 1,0,1 (currently "Bike Racks"). It also formats the returned value in Bold.
Once you have Set your function, the variable is added to the canvas with a highlighted, green background (purple arrow).
Dynamic PQL Functions
The Pyramid Query Language (PQL), pronounced "Prequel," is a language built into Pyramid's PYRANA engine to allow users to construct queries that can run against the many SQL data stacks that Pyramid can query natively. PQL includes a vast array of operations and functions that allow users to query data and build analytical logic.
The Dynamic Text Editor exposes Dynamic and Common functions. Dynamic functions are a set of specialized methods that are designed to act on a given query's result set. Common functions are a set of methods that are found in most programmatic and mathematical function libraries. These functions are used to perform logic on basic data without concern for data structures, context, or even source.
To add a PQL function from the library to the script editor, simply double click on it. You will then need to inject values into the function.
Using the LLM AI-Driven PQL Function
Pyramid's Generative AI integration enables you to use AI to generate scripts and images, dynamic text and infographics, calculations and lists, schedules, colors, and more. It allows you to generate a range of content, including complex code, simply by providing a text prompt.
Tip: You can use the LLM PQL function (from the Common PQL functions) to generate dynamic text, adding the results to custom tooltips, text fields, or into your PQL formulas. While this function cannot tell you about your data set, it can add additional information and background to your presentation or publication.
Warning: When using LLMs, your assets are generated using public domain algorithms. This can produce erroneous and inconsistent or random results. Use at your own risk.
Example: Build Dynamic Text
In this example, the dynamic text will be driven by a grid listing sales by manufacturer, in descending order. The text should display the name of the top manufacturer, and its sales.
Step 1
Add a textbox by clicking the Textbox button, and then clicking anywhere on the canvas:
Step 2
Write your text in the textbox. In this example, the text is 'Manufacturer sales were x'; both 'manufacturer' and 'x' will be made dynamic.
After entering the text, highlight the word 'manufacturer' and click the Dynamic Text button from the Component ribbon:
After clicking 'Dynamic Text', the Dynamic Text Function Editor will open; this is where you'll configure your dynamic text formula.
Step 3
Your dynamic text must be based on an existing grid. Open the Content panel, find and select the content you want to base your dynamic text on. Once selected, the grid will appear in the editor.
Create the dynamic text formula by selecting columns, rows, or cells from the columns, and applying functions to the selection from the Function trees (on the right).
Step 4
Click the cell that represents the value or element you want to add to your dynamic text.
In this example, this is the cell containing the 'Acme' header. Once the cell has been added in the next window, click the test button to verify your selection, or click Apply to confirm your selection and close the editor.
Step 5
Once applied, the dynamic text will appear in your textbox.
If your statement contains multiple dynamic elements, repeat steps 2-5.
In this example, the corresponding sales value for the Acme must be added as a dynamic element, so highlight 'x' from the textbox and click Dynamic Text.
Step 6
Find the same grid in the Content tree, and select the cell containing the sales for Acme:
Step 7
Test your selection (blue arrow).
If selecting numeric values, you can specify the format. In this example, the amount returned (orange arrow) contains too many decimal places. In order to change the formatting and reduce the number of decimal places, a dynamic function will be applied.
Highlight the data cell from the function editor (blue arrow), open the Common functions tree, and select 'CurrencyDollars0' (green highlights) to change the formatting to dollars with no decimal places.
Now test the function to see a preview, and then click Apply to confirm the function:
Step 8
Your dynamic text is displayed in the textbox; save your text for later use in the Present and Publish modules.
Using Tabulate for Multiple Source Grids
Users requiring dynamic text from disparate data based on multiple queries can use Tabulate as the source for the dynamic text. Tabulate allows you to blend queries from multiple data sources into a single Tabulation. You must then define a Visual Area that can be used as a single data source for the dynamic text. The Visual Area can then be used as a source for the data grid that the dynamic text is based on.
Example: Using Tabulate for a Multiple Source Grid
Step 1
In Tabulate, drag and drop different queries onto the canvas. In this example Sales and Returns is dropped on the left hand side of the Tabulate canvas and the Expenses and Margin query is dropped on the right hand side of the canvas.
Step 2
Select the required area including both queries.
Step 3
Set the Visual Area by clicking on the Set Visual Area button from the Visual Area drop-down on the Tabulate ribbon, name the visual area and save the Tabulate worksheet
Note that you must save the Visual Area in the Tabulation and the Tabulate worksheet in order to use it in an Illustrate item.
Step 4
Build your dynamic text (as per previous example), select the Visual Area as the source for the data grid.
From the Content drop down, select the Country Sales and Expenses Tabulate item, then select the Visual Area - "Sales_And_Expenses". This will be used as the data grid for the dynamic text.
Step 5
You can now use any data from the Visual Area in your dynamic text. In this dynamic text formula (orange box) we have subtracted New South Wales Expenses (green box) from New South Wales sales (red box). The result of $47,051.61 can be seen in the dynamic text (orange box). This demonstrates how data from disparate sources can be used to generate dynamic text.
